mysql 高级应用


窗口函数
	基本用法
		函数（） over （）
		函数名（） over（ partition by 要分的组 order by 要排序的组 rows between 数据范围）
		sum( C ) over (partition by A order by B rows between D1 and D2)
		C=需要计算的字段
		A=分组字段名称
		B=排序字段名称
		D1，D2 = 计算的行数范围
			rows between 2 preceding and current row # 取当前行和前面两行
			rows between unbounded preceding and current row  # 包括本行和之前所有的行
			rows between current row and unbounded following # 包括本行和之后所有的行
			rows between 3 preceding and current row  # 包括本行和前面三行
			rows between 3 preceding and 1 following  # 从前面三行和下面一行，总共五行
			# 当order by后面缺少窗口从句条件，窗口规范默认是rows between unbounded preceding and current row.
			# 当order by和窗口从句都缺失, 窗口规范默认是 rows between unbounded preceding and unbounded following

	窗口函数的应用
		专有窗口函数：
			rank（）
			dense_rank()
			row_number()
		聚合类窗口函数
			普通场景下，聚合函数往往和group by 一起使用，但是窗口环境下，聚合函数也可以应用进来，此时就被称为聚合类窗口函数，属于窗口函数的一种
			sum()	count()		avg() 	max() 	min()	
		窗口函数（专有窗口函数+聚合类窗口函数）和普通场景下的聚合函数也很容易混淆，二者区别如下：
			1 普通场景下的聚合函数是将多条记录聚合为一条 （多到一）
			  窗口函数是每条记录都会执行，有几条记录执行完韩式几条 （多到多）
			2 分组（partition by） 记录按照字段进行分组，窗口函数在不同的分组上分别执行。
			3 排序（order by）按照哪些字段进行排序，窗口函数将按照排序后的记录排序进行标号，可以和partition子句配合使用，也可以单独使用，如果没有partition子句，数据范围是整个表的数据行
			4 窗口（rows）就是进行函数分析时要处理的数据范围，数据当前分区的一个子集，通常用来作为滑动窗口使用，比如要分局每个订单动态计算包括本订单和按时间顺序前后两个订单的移动平均支付金额，则可以设置rows句子来创建滑动窗口（rows）
		排序函数
			row_number() over()
				会为查询出来的每一行记录生成一个序号，一次排序且不会重复
			rank() over()
				字段值相同的排序序号是一样的 后面字段值不相同的序号将跳过相同的排名号排下一个，序号可能不连续
			dense_rank() over()
				字段值相同的排序序号是一样的 后面字段值不相同的序号将跳过相同的排名号排下一个，序号连续
			ntile（n） over（）
				用于将数据分组切成n片，不支持 rows between
		偏移分析函数
			lag（） over（） 下行
				lag（字段，offset，空值填充） over （partition by 。。 order by 。。）
			lead（） over（） 上行
				lead（字段，offset，空值填充） over （partition by 。。 order by 。。）
















#创建数据库
create database taks2_2 CHARACTER set utf8
#创建表
create table user_trade(
	user_name VARCHAR(20),
	piece int,
	price DOUBLE,
	goods_category VARCHAR(20),
	pay_time date
);

ALTER table user_trade add pay_amount DOUBLE;

#需求1 查询出2019年每月的支付总额和当年的累计支付总额

#step 1#################### 
SELECT
*
FROM
	user_trade AS ut 
WHERE
	YEAR ( ut.pay_time ) = 2019 ;

#setp 2####################
SELECT
	MONTH ( ut.pay_time ),
	sum( ut.pay_amount ) 
FROM
	user_trade AS ut 
WHERE
	YEAR ( ut.pay_time ) = 2019 
GROUP BY
	MONTH ( ut.pay_time );
	
#step 3#####################

SELECT
	a.MONTH,#月份
	a.pay_amount,#当月支付总金额
	sum( pay_amount ) over ( ORDER BY a.MONTH ) as '累计'
FROM
	(
	SELECT MONTH
		( ut.pay_time ) AS MONTH,
		sum( ut.pay_amount ) AS pay_amount 
	FROM
		user_trade AS ut 
	WHERE
		YEAR ( ut.pay_time ) = 2019 
	GROUP BY
		MONTH ( ut.pay_time )

) a;


#需求2 查询出2018-2019年每月的支付总额和当年累计支付总额
SELECT
	a.YEAR,
	a.MONTH,
	a.amount,
	sum( a.amount ) over ( PARTITION BY a.YEAR ORDER BY a.MONTH ) 
FROM
	(
	SELECT YEAR
		( pay_time ) AS YEAR,
		MONTH ( pay_time ) AS MONTH,
		sum( pay_amount ) AS amount 
	FROM
		user_trade 
	WHERE
		YEAR ( pay_time ) IN ( 2018, 2019 ) 
	GROUP BY
		YEAR ( pay_time ),
		MONTH ( pay_time ) 
	) AS a;
	
	
	
# 需求3 查询出2019年每个月的近三月移动平均支付金额

SELECT
	a.MONTH,
	a.amount,
	avg( a.amount ) over ( ORDER BY a.MONTH rows BETWEEN 2 preceding AND current ROW ) AS avg 
FROM
	(
	SELECT MONTH
		( u.pay_time ) AS MONTH,
		sum( u.pay_amount ) AS amount 
	FROM
		user_trade AS u 
	WHERE
		YEAR ( u.pay_time ) = 2019 
	GROUP BY
		MONTH ( u.pay_time ) 
	) AS a;


#需求4 查询出每四个月的最大月总支付金额
SELECT
	a.time,
	a.amount,
	max(a.amount) over(ORDER BY a.time rows BETWEEN 3 preceding and current row) as max

FROM
(
SELECT
	substring( u.pay_time, 1, 7 ) as time,
	sum( u.pay_amount ) as amount 
FROM
	user_trade AS u 
GROUP BY
	substring( u.pay_time, 1, 7 )
	) as a ;



#需求5 2020年1月，购买商品品类数的用户排名

SELECT
	a.name,
	a.count,
	row_number() over (ORDER BY a.count ) as order_1,
	rank() over (ORDER BY a.count) as order_2,
	dense_rank() over (ORDER BY a.count) as order_3
FROM


(
SELECT
	u.user_name as name,
	count( DISTINCT u.goods_category) as count
-- 	count(u.goods_category)
FROM
	user_trade as u
WHERE
	substring(u.pay_time,1,7)='2020-01'
GROUP BY
	u.user_name
	) as a；



#需求6 查询出将2020年2月的支付用户，按照支付金额分成5组后的结果

SELECT
	u.user_name as name,
	sum(u.pay_amount) as amount,
	ntile(5) over (ORDER BY sum(u.pay_amount) DESC) as level
FROM
	user_trade as u
WHERE
	SUBSTR(u.pay_time,1,7)='2020-02'
GROUP BY
	u.user_name；


#需求7 查询出2020年支付金额排名前30%的所有用户

SELECT
	a.name,
	a.money,
	a.level
FROM

(
SELECT
	u.user_name as name ,
	sum(u.pay_amount) as money,
	ntile(10) over (ORDER BY sum(u.pay_amount) desc) as level

FROM
	user_trade as u
WHERE
	YEAR(u.pay_time)=2020
GROUP BY
	u.user_name
	) as a

WHERE 
	a.level in (1,2,3)；




#需求10 查询出支付时间间隔超过100天的用户

SELECT
	count(DISTINCT a.name)
-- a.name
FROM
(
SELECT
	u.user_name as name ,
	u.pay_time as pay,
	lead(u.pay_time,1) over(PARTITION by u.user_name ORDER BY u.pay_time) as lead_dt
	
FROM
	user_trade as u
) as a
WHERE 
	a.lead_dt is not null 
and datediff(a.lead_dt,a.pay)>100
；






Mysql 索引
	


